﻿using hoemwork;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using System.Transactions;

namespace fg
{
    class Program
    {
        public static void EnrollFunction()
        {


            ///1链接数据库connection
            string connectionString =
                @"Data Source=(localdb)\MSSQLLocalDB;
                Initial Catalog=18bang;
                Integrated Security=True;";                                        //链接数据库
            using (TransactionScope scope= new TransactionScope())
            {

            }
            using (IDbConnection connection = new SqlConnection(connectionString))//使用using自动关闭链接
            { connection.Open();
                using (IDbTransaction transaction = connection.BeginTransaction())  //connection.BeginTransaction方法返回IDBTracsaction对象
                {
                    try
                    {
                        IDbCommand command = new SqlCommand();//声明命令的对象
                        command.Connection = connection;
                        command.Transaction = transaction;//事务对象赋值给命令对象的事务属性
                        command.CommandText = "UPDATE Student SET Age -= 1;";//使用sql更改语句
                        command.ExecuteNonQuery();//执行语句
                                                  //IDbCommand newCommand = new SqlCommand();
                                                  //newCommand.Transaction=transaction;
                                                  //会违反Age>0的约束
                        command.CommandText = "UPDATE Student SET Age -= 8;";
                        command.ExecuteNonQuery();

                        transaction.Commit(); //事务的提交
                    }
                    catch (Exception)
                    {
                        transaction.Rollback(); // 事务回滚
                        //注意不能对异常“不予处理”，直接吞了
                        throw;
                    }
                }



                //链接打开
                //IDbCommand command = new SqlCommand();                            //声明命令对象
                //command.Connection = connection;                                  //命令的链接 为链接
                //                                                                  //  模拟登陆1用户名是否存在2密码是否正确
                ////command.CommandType = CommandType.StoredProcedure;
                //command.CommandText = 
                //    $"INSERT Teacher([Name]) VALUES(N'源栈小飞哥')" +
                //    $" SELECT @@IDENTITY";
                ////IDataParameter pScore = new SqlParameter("@score", 80);//声明对象pScore构造函数传值
                ////command.Parameters.Add(pScore);     //command是IDBCommand的对象parameters是只读属性 ADD是方法
                ////IDataParameter pCount = 
                ////new SqlParameter("@count", SqlDbType.Int) //生名对象pCount构造函数传值类型是sql.int类型
                ////{
                ////    Direction = ParameterDirection.Output //Direction 被枚举类ParameterDirection.Output赋值
                ////};
                ////command.Parameters.Add(pCount);
                ////command.ExecuteScalar();
                // Console.WriteLine(command.ExecuteScalar());

            }



        }










        /////1链接数据库connection
        //string connectionString = 
        //    @"Data Source=(localdb)\MSSQLLocalDB;
        //    Initial Catalog=18bang;
        //    Integrated Security=True;";//链接数据库
        //using (IDbConnection connection = new SqlConnection(connectionString))//使用using自动关闭链接
        //{
        //    connection.Open();                                  ///链接打开
        //    IDbCommand command = new SqlCommand();               //声明命令对象
        //    command.Connection = connection;                      //命令的链接 为链接
        //    //模拟登陆1用户名是否存在2密码是否正确  
        //    command.CommandText =                                 //命令的文本形式
        //        $"SELECT [Password] FROM [User]" +               
        //        $" WHERE[UserName] = '{name}' ";
        //    object dbpassword = command.ExecuteScalar()?.ToString();  // 取出命令的返回值
        //    if (dbpassword == null)                                  //判断密码是否为空
        //    {
        //        Console.WriteLine("用户名不存在");
        //        return;
        //    }
        //    if (dbpassword.ToString() != password)                    //判断命令的返回值是否和数据库中的密码一样    
        //    {
        //        Console.WriteLine("*用户名或密码错误");
        //        return;
        //    }
        //}


        //  IDbConnection connection = new SqlConnection(connectionString);
        //   IDbCommand command = new SqlCommand();
        //   command.Connection = connection;
        // command.CommandText = "SELECT [Name] FROM City WHERE ID=5 ";
        //  command.CommandText = "SELECT * FROM City"; 
        //try
        //{
        //    connection.Open();
        //    IDbCommand command = new SqlCommand();
        //    command.Connection = connection;
        //    //模拟登陆1用户名是否存在2密码是否正确
        //    command.CommandText = $"SELECT [password] FROM [User]" +
        //        $" WHERE[UserName] = {name}";
        //    object dbpassword = command.ExecuteScalar()?.ToString();
        //    if (dbpassword == null)
        //    {
        //        Console.WriteLine("用户名不存在");
        //        return;
        //    }
        //    if (dbpassword.ToString() != password)
        //    {

        //    }


        // +
        //    // $"AND Password = {password}  ";
        //    // reader.Read();
        //    //    while (reader.Read())
        //    //    {  
        //    //        for (int i = 0; i < reader.FieldCount; i++)
        //    //        {
        //    //            Console.Write(reader[i]+"     ");
        //    //        }
        //    //        Console.WriteLine();
        //    //    }
        //}
        //catch (Exception)
        //{

        //    throw;
        //}
        //finally
        //{
        //    connection.Close();
        //    connection.Dispose();
        //    connection = null;
        //}
        //connection.Open();




        //using (DbConnection connection = new SqlConnection(connectionString))
        //{
        //    connection.Open();  //需要显式的Open()
        //                        //进行其他操作
        //}
        //pool池
        ///1.尽可能晚的打开，尽可能早关闭 ：短链接
        ///2.反之，长链接;进行多次操作
        ///性能的问题没有银弹

        ///2生成数据库命令（增删改查）command
        ///3执行和（返回结果）
        ///4关闭数据库
        //Data Source = (localdb)\MSSQLLocalDB; Initial Catalog = 18bang; Integrated Security = True;
        public static Task<int> GetRandom()
        {

            Task<int> task = Task.Run(() =>
            {

                for (int i = 0; i < 10; i++)
                {
                    Thread.Sleep(5);
                    Console.WriteLine($"         in  GetRandom().{i}");
                }

                return new Random().Next(1000);
            }); for (int i = 0; i < 20; i++)
            {
                Thread.Sleep(3);
                Console.WriteLine($" int GetRandom()after Task<int>.{i}...");
            }
            return task;
        }

        //public static /*async*/ void Process()
        //{
        //    //Task<int> task = getRandom();
        //    //Console.WriteLine("getRandom()="+task.Result);


        //    Console.WriteLine("in Process() before await....");
        //    //   int Result =await GetRandom();
        //    int random = GetRandom();
        //    Console.WriteLine("   result:" + random);
        //    Console.WriteLine("int Process()  after await....");
        //}

        static void Main(string[] args)
        {

            EnrollFunction();















            //Console.WriteLine("before Process()....");

            //for (int i = 0; i < 20; i++)
            //{
            //    Thread.Sleep(15);
            //    Console.WriteLine($"after Process().{i}....");
            //}
            //Thread.Sleep(1000);








            //string directory = @"D:\C#语法\作业点评";
            //string filename = @"log";
            //Console.WriteLine(Directory.Exists(directory));
            //string path = Path.Combine(directory,"fg");
            //Directory.CreateDirectory(Path.Combine(directory, "fg"));
            //string fullFilrName = Path.Combine(path,filename);
            //FileStream stream = new FileStream(fullFilrName,FileMode.Open);
            //byte[] array = new byte[100];
            //stream.Read(array, 0, 100);

            //Thread current = Thread.CurrentThread;
            //Console.WriteLine(Thread.GetDomain().FriendlyName);  //当前线程所在进程
            //Console.WriteLine(current.ManagedThreadId);     //托管线程Id
            //Console.WriteLine(current.Priority);            //优先级
            //Console.WriteLine(current.ThreadState);         //线程状态
            //Console.WriteLine(current.IsThreadPoolThread);
            //Console.WriteLine();
            //Console.WriteLine();
            //Console.WriteLine();
            // Thread thread = new Thread(Process);
            //  thread.Start();
            //Console.WriteLine(Thread.GetDomain().FriendlyName);  //当前线程所在进程
            //Console.WriteLine(thread.ManagedThreadId);     //托管线程Id
            //Console.WriteLine(thread.Priority);            //优先级
            //Console.WriteLine(thread.ThreadState);         //线程状态
            //Console.WriteLine(thread.IsThreadPoolThread);


            //for (int i = 0; i < 20; i++)
            //{
            //    //同步：Console.WriteLine($"{i}：ThreadId-{Thread.CurrentThread.ManagedThreadId}"); 
            //    new Thread(() =>
            //    {
            //        Console.WriteLine($"{i}：ThreadId-{Thread.CurrentThread.ManagedThreadId}");
            //    }).Start();
            //}



            //        Teacher fg = new Teacher { Age = 19, Name = "大飞哥" };
            //        Teacher fish = new Teacher { Age = 23, Name = "小鱼" };
            //        Teacher dfg = new Teacher { Age = 39, Name = "大飞哥" };
            //        IEnumerable<Teacher> teachers = new List<Teacher> { fg, fish, dfg };

            //        Major csharp = new Major { Name = "C#", Age = 19, TeacherName = "大飞哥" };
            //        Major SQL = new Major { Name = "SQL", Age = 19, TeacherName = "大飞哥" };
            //        Major Javascript = new Major { Name = "Javascript", Age = 39, TeacherName = "大飞哥" };
            //        Major UI = new Major { Name = "UI", Age = 23, TeacherName = "小鱼" };
            //        IEnumerable<Major> majors = new List<Major> { csharp, SQL, Javascript, UI };

            //        IList<Student> students = new List<Student>
            //{
            //    new Student{Score = 98, Name = "屿", Majors=new List<Major>{csharp,SQL } },
            //    new Student{Score = 86, Name = "行人", Majors=new List<Major>{Javascript, csharp, SQL} },
            //    new Student{Score = 78, Name = "王平", Majors=new List<Major>{csharp}},
            //    new Student{Score = 89, Name = "王枫", Majors=new List<Major>{Javascript, csharp, SQL,UI}},
            //    new Student{Score = 98, Name = "蒋宜蒙", Majors=new List<Major>{Javascript, csharp}},
            //};
            //        var majores = from s in students
            //                      let ms = s.Majors   //把所有的 Major 先暴露出来
            //                      from m in ms        //后面就可以使用 let 指定的 ms
            //                      select new { student = s.Name, major = m };
            //        var result = majors.Join(teachers, 
            //            m => m.TeacherName,
            //            t => t.Name, 
            //            (m, t) => new {tName=t.Name ,mName=m.Name });
            //var result = majors.GroupBy(s => s.TeacherName);
            //foreach (var item in result)
            //{
            //    Console.WriteLine($"{item.Key}带了{item.Count()}名学生");
            //}
            // var result = students.Where(s => s.Score > 60);
            //IList<IGrouping<Teacher,Student>> lstStudents = result.ToList();
            if (true)
            {
                //result = result.Where(r => r.Name.StartsWith("王"));
            }
            if (true)
            {

            }
            //var ms = from t in teachers    //List<Teacher>放在第一位
            //         join m in majors
            //         on t.Id equals m.TeacherId
            //         into mt            //又见into，mt是IEnumerable<Major>
            //         from result in mt.DefaultIfEmpty()       //调用了DefaultIfEmpty()并再次from
            //         select new { teacher = t.Name, major = result?.Name ?? "没有课程" };
            //var ms = from t in teachers    //List<Teacher>放在第一位
            //         join m in majors
            //     on new { t.Age, t.Name } equals new { m.Age, Name = m.TeacherName }
            //         select new { teacher = t.Name, major = m.Name, Age = t.Age };
            //foreach (var item in ms)
            //{
            //    Console.WriteLine(item.major + "" + item.teacher + "" + item.Age);
            //}
            //var excellent = from s in students
            //                select new //ShortStudentInfo
            //                {
            //                    /*Name =*/
            //                    s.Name,
            //                    /* Score = */
            //                    s.Score
            //                };
            //foreach (var item in excellent)
            //{
            //    Console.WriteLine(item.Name);
            ////}
            //var result = from m in majors
            //             from t in teachers
            //                 //  join t in teachers on m.TeacherId equals t.Id
            //                 // where t.Name == "小鱼"
            //             select new
            //             {
            //                 t.Id,
            //                 teacherName = t.Id,
            //                 majorName = m.Name

            //             };
            //foreach (var item in result)
            //{
            //    Console.WriteLine(item.Id + item.teacherName + item.majorName);
            //}
            //into gm
            //select new KeyValuePair<Teacher, int>(gm.Key, gm.Count());
            //select new
            //{
            //    gm.Key,
            //    Count = gm.Count()
            //};
            //var lststat = stat.ToDictionary(s => s.Key);
            //foreach (var item in stat)
            //{
            //    Console.WriteLine(item.Key.Name + "" + item.Count);
            //}
            //List<Student> students = new List<Student> {
            // new Student (){name="ls" }, new Student(){name="fg" }, new Student(){name="hyl" }
            //};
            //foreach (var item in students)
            //{
            //    //item = new Student();这行不能行。
            //    students[0].name = "ls";
            //}
            //for (int i = 0; i < students.Count; i++)
            //{
            //    students[i].name = "ls";
            //}
            //IEnumerator<Student> enumerator = students.GetEnumerator();
            ////while (enumerator.MoveNext())
            ////{
            ////    var item = enumerator.Current;
            ////    Console.WriteLine(item);
            ////}
            //for (int i = 0; i < students.Count; i++)
            //{
            //    Console.WriteLine(students[i].name);
            //}
            //foreach (Student item in students)//var指明item的类型
            //{
            //    Console.WriteLine(item.name);
            //}
            ////foreach (var item in new Student())
            ////{

            //}
            //Dictionary<string, Student> students = new Dictionary<string, Student>//Dictionary是键值队类型
            //{   {"ls",new Student() },
            //    {"fg",new Student() },
            //    {"hyl",new Student() }

            //};
            ////for (int i = 0; i < students.Count; i++)
            ////{
            ////    Console.WriteLine(students[i]);
            ////}
            //foreach (var item in students)//item集合中的某个元素   collection 在哪个集合中进行迭代
            //{
            //    Console.WriteLine(item.Key);
            //}

        }
    }
}
struct ShortStudentInfo
{
    public int Score { get; set; }
    public string Name { get; set; }
}